<?php

// phpMyRealty 3
//
// File Name: cron.inc.php
// File Location : ./includes/functions/
//
// Copyright (c)2009 phpMyRealty.com
//
// e-mail: support@phpMyRealty.com

// Getting the latest visitors counter and timestamp from the cron table
$sql = 'SELECT * FROM ' . CRON_TABLE . ' LIMIT 1';
$r = $db->query($sql) or error ('Critical Error', mysql_error ());
$f = $db->fetcharray($r);

// Getting the date of the last visitor visit
$old_date = date ('Y-m-d', $f['time']);

// Getting today's date
$new_date = date ('Y-m-d');

// Updating the cron table with the new visitor details
$sql = 'UPDATE ' . CRON_TABLE . ' SET counter = counter + 1, time = "' . time() . '"';
$db->query($sql) or error ('Critical Error', mysql_error ());

// If today is a different date we start the CRON jobs
if ($new_date > $old_date)
 {
  // LISTINGS

    // Expire or delete the free listings
    if ($conf['free_listings_expire'] > 0 && is_numeric($conf['free_listings_expire'])) {
        $sql = 'SELECT id, type FROM '.PROPERTIES_TABLE.' 
                WHERE approved = 1 
                      AND DATE_ADD(date_approved, INTERVAL '.$conf['free_listings_expire'].' DAY) < NOW()';
        $r = $db->query($sql) or error('Critical Error', mysql_error());
    
        while ($f = $db->fetcharray($r)) {
            // If set to delete
            if ($conf['expired_listings'] == '2') {
                removeuserlisting($f['id']);
            } else {
                update_categories($f['type'], '');

                // Set to 'expired' which is approved = 2 
                $sql = "UPDATE " . PROPERTIES_TABLE . " 
                        SET date_approved = NULL,
                            approved = '2' 
                        WHERE id = '" . $f['id'] . "' 
                        LIMIT 1";
                $ul = $db->query($sql);
            }
        }
        $db->freeresult($r);
     }

  // Fetching the featured listings to check if there are
  // listings to make featured or remove from the featured
  // list
  $sql = 'SELECT * FROM ' . FEATURED_TABLE;
  $r = $db->query($sql) or error ('Critical Error', mysql_error ());

  while ($f = $db->fetcharray($r))
   {
    // If this listing needs to become featured today or
    // it was not converted into the featured one yet
    if ($f['start_date'] <= date('Y-m-d') && $f['featured'] != 1)
     {
      // Updating the featured table 
      $sql = 'UPDATE ' . FEATURED_TABLE . ' SET featured = 1 WHERE id = ' . $f['id'] . ' LIMIT 1';
      $db->query($sql) or error ('Critical Error', mysql_error ());

      // Updating the users table 
      $sql = 'UPDATE ' . PROPERTIES_TABLE . ' SET featured = "A" WHERE id = ' . $f['id'] . ' LIMIT 1';
      $db->query($sql) or error ('Critical Error', mysql_error ());

     }

    // If this listing needs to expire today or
    // it was not converted into the free one yet
    if ($f['end_date'] <= date('Y-m-d') && $f['featured'] == 1)
     {
      // Updating the featured table 
      $sql = 'DELETE FROM ' . FEATURED_TABLE . ' WHERE id = ' . $f['id'] . ' LIMIT 1';
      $db->query($sql) or error ('Critical Error', mysql_error ());

      // Updating the users table 
      $sql = 'UPDATE ' . PROPERTIES_TABLE . ' SET featured = "B" WHERE id = ' . $f['id'] . ' LIMIT 1';
      $db->query($sql) or error ('Critical Error', mysql_error ());

     }

   }

  // AGENTS

  // Fetching the agents listings to check if there are
  // listings to make featured or remove from the featured
  // list
  $sql = 'SELECT * FROM ' . FEATURED_AGENTS_TABLE;
  $r = $db->query($sql) or error ('Critical Error', mysql_error ());

  while ($f = $db->fetcharray($r))
   {

    // If this listing needs to expire today or
    // it was not converted into the free one yet
    if ($f['end_date'] <= date('Y-m-d') && $f['featured'] == 1)
     {

      // Updating the featured table 
      $sql = 'DELETE FROM ' . FEATURED_AGENTS_TABLE . ' WHERE id = ' . $f['id'] . ' LIMIT 1';
      $db->query($sql) or error ('Critical Error', mysql_error () );

      // Updating the users table 
      $sql = 'UPDATE ' . USERS_TABLE . ' SET package = "" WHERE id = ' . $f['id'] . ' LIMIT 1';
      $db->query($sql) or error ('Critical Error', mysql_error () );

      //

		$sql = 'SELECT * FROM ' . PROPERTIES_TABLE . ' WHERE userid = ' . $f['id'] . ' ORDER BY id DESC';
		$rl = $db->query($sql);
		$listings_number = $db->numrows($rl);
		
		if ($listings_number > $conf['free_listings'])
		{
		
		  // Remove listings or expire listings based on the number they are over (start with most recent offenders)
		  $count = 1;
		  
		  while ($fl = $db->fetcharray($rl))
		  {

			 if ($count <= $conf['free_listings'])
			 {
			  	// If set to delete
			  	if ($conf['expired_listings'] == '2')
			  		removeuserlisting($fl['id']);
			  	else
			  	{
			  		// Set to 'expired' which is approved = 2 
			  		$sql = "UPDATE " . PROPERTIES_TABLE . " 
			  		        SET date_approved = NULL,
			  		            approved = '2'
			  		        WHERE id = '" . $fl['id'] . "' 
			  		        LIMIT 1";
			  		$ul = $db->query($sql);
			  	}
			  }
			 $count++;
		  }
		}

      $sql = 'SELECT * FROM ' . PROPERTIES_TABLE . ' WHERE approved = 1 AND userid = ' . $f['id'] . ' ORDER BY id DESC';
      $rl = $db->query($sql);
   
      $count = 1;
      while ($fl = $db->fetcharray($rl))
       {
       
        if ($count <= $conf['free_listings'])
        {
	        $sql = 'SELECT * FROM ' . GALLERY_TABLE . ' WHERE listingid = ' . $fl['id'];
	        $rg = $db->query($sql);
	
	        if ($db->numrows($rg) > $conf['free_gallery'])
	         {
	
				while ($fg = $db->fetcharray($rg))
				{
				
					// If set to delete
					if ($conf['expired_listings'] == '2')
					{
						removelistingimage('gallery', $fg['id']);
					
						$sql = 'DELETE FROM ' . GALLERY_TABLE . ' WHERE listingid = ' . $fl['id'];
						$db->query($sql);
					}
				}
	         }
		}
         
         $count++;

       }

     }

   }


  // Notify listings which are about to expire (in X days)

  $sql = 'SELECT * FROM ' . FEATURED_TABLE . ' WHERE featured = 1 AND ( (( TO_DAYS(end_date) - TO_DAYS(start_date) ) - ( TO_DAYS(NOW()) - TO_DAYS(start_date) )) <= ' . $conf['expire_notice'] . ')';
  $r = $db->query($sql) or error ('Critical Error', mysql_error ());

  while ($f = $db->fetcharray($r))
   {

    $sql = 'SELECT * FROM ' . PROPERTIES_TABLE . ' WHERE id = ' . $f['id'];
    $re = $db->query($sql) or error ('Critical Error', mysql_error ());
    $fe = $db->fetcharray($re);

    $sql = 'SELECT * FROM ' . USERS_TABLE . ' WHERE id = ' . $fe['userid'];
    $ru = $db->query($sql) or error ('Critical Error', mysql_error ());
    $fu = $db->fetcharray($ru);

//

    $mail = new PHPMailer();

    if(PHPMAILER == '3') {
     $mail->IsSMTP(); // set mailer to use SMTP
     $mail->Host = $smtp['host'];  // specify main and backup server
     $mail->SMTPAuth = true;     // turn on SMTP authentication
     $mail->Username = $smtp['login'];  // SMTP username
     $mail->Password = $smtp['password']; // SMTP password
    }
    elseif(PHPMAILER == '2') {
     $mail->IsSendmail(); // set mailer to use SMTP
    }
    else {
    }


    $mail->From = $conf['general_e_mail'];
    $mail->FromName = $conf['general_e_mail_name'];
    $mail->AddAddress($fu['email']);

    $mail->Subject = $lang['Listing_Expiration_Subject'];

    $mailout_listing_expiration = $lang['Listing_Expiration_Email'];

    $mailout_listing_expiration = str_replace('{name}', $fu['first_name'] . ' ' . $fu['last_name'] , $mailout_listing_expiration);
    $mailout_listing_expiration = str_replace('{listing}', $fe['title'], $mailout_listing_expiration);
    $mailout_listing_expiration = str_replace('{date}', printdate($f['end_date']), $mailout_listing_expiration);
    $mailout_listing_expiration = str_replace('{website}', $conf['website_name'], $mailout_listing_expiration);

    $mail->MsgHTML = $mailout_listing_expiration;
    $mail->AltBody = removehtml($mailout_listing_expiration);

    $mail->Send();
     
   }

  // Notify agents which are about to expire (in X days)

  $sql = 'SELECT * FROM ' . FEATURED_AGENTS_TABLE . ' WHERE featured = 1 AND ( (( TO_DAYS(end_date) - TO_DAYS(start_date) ) - ( TO_DAYS(NOW()) - TO_DAYS(start_date) )) <= ' . $conf['expire_notice'] . ')';
  $r = $db->query($sql) or error ('Critical Error', mysql_error ());

  while ($f = $db->fetcharray($r))
   {

    $sql = 'SELECT * FROM ' . USERS_TABLE . ' WHERE id = ' . $f['id'];
    $ru = $db->query($sql) or error ('Critical Error', mysql_error ());
    $fu = $db->fetcharray($ru);

//
     
    $mail = new PHPMailer();

    if(PHPMAILER == '3') {
     $mail->IsSMTP(); // set mailer to use SMTP
     $mail->Host = $smtp['host'];  // specify main and backup server
     $mail->SMTPAuth = true;     // turn on SMTP authentication
     $mail->Username = $smtp['login'];  // SMTP username
     $mail->Password = $smtp['password']; // SMTP password
    }
    elseif(PHPMAILER == '2') {
     $mail->IsSendmail(); // set mailer to use SMTP
    }
    else {
    }


    $mail->From = $conf['general_e_mail'];
    $mail->FromName = $conf['general_e_mail_name'];
    $mail->AddAddress($fu['email']);

    $mail->Subject = $lang['Agent_Expiration_Subject'];

    $mailout_agent_expiration = $lang['Agent_Expiration_Email'];

    $mailout_agent_expiration = str_replace('{name}', $fu['first_name'] . ' ' . $fu['last_name'] , $mailout_agent_expiration);
    $mailout_agent_expiration = str_replace('{date}', printdate($f['end_date']), $mailout_agent_expiration);
    $mailout_agent_expiration = str_replace('{website}', $conf['website_name'], $mailout_agent_expiration);

    $mail->MsgHTML = $mailout_agent_expiration;
    $mail->AltBody = removehtml($mailout_agent_expiration);

    $mail->Send();

   }

 }

?>